package cn.com.pc.content.service;

import cn.com.pc.content.constant.TagConstants;
import cn.com.pc.content.domain.Biz;
import cn.com.pc.content.domain.Site;
import cn.com.pc.content.domain.tag.*;
import cn.com.pc.content.dto.TagDTO;
import cn.com.pc.content.dto.TagRelationDTO;
import cn.com.pc.content.service.es.ESCurdService;
import cn.com.pc.content.util.JDBCUtils;
import cn.com.pc.content.util.LocalDateUtils;
import cn.com.pc.content.util.OJDBCUtils;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.elasticsearch.rest.RestStatus;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.time.Duration;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@Service
public class TagImportService {

    @Autowired
    private TagRepository tagRepository;

    @Autowired
    private TagTreeRepository tagTreeRepository;

    @Autowired
    private TagRelationRepository tagRelationRepository;

    @Autowired
    private ESCurdService esCurdService;

    @Autowired
    private TagService tagService;

    @Autowired
    private TagRelationService tagRelationService;

//    @Async
    @SneakyThrows
    public void importTagsFromPcpDB() {
        Connection conn = JDBCUtils.getConnection();
        String countSql = "SELECT count(*) FROM pcp_tag";
        PreparedStatement countStmt = conn.prepareStatement(countSql);
        ResultSet countResultSet = countStmt.executeQuery();
        int count = 0;
        while (countResultSet.next()) {count = countResultSet.getInt(1);}
        JDBCUtils.close(countResultSet, countStmt, null);

        int pageSize = 10000;
        int totalPage = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;
        String sql = "SELECT * FROM pcp_tag t JOIN pcp_tag_cat c ON t.id=c.tag_id WHERE t.status!=-1 ORDER BY CASE domain " +
                "WHEN '品类' THEN 1 " +
                "WHEN '品牌' THEN 2 " +
                "WHEN '产品' THEN 3 " +
                "ELSE 4 " +
                "END, parent_id ASC LIMIT ? OFFSET ?";
        PreparedStatement stmt = conn.prepareStatement(sql);

        int i = 0; LocalDateTime t = LocalDateTime.now();
        for (int p = 0; p < totalPage; p++) {
            stmt.setInt(1, pageSize);
            stmt.setInt(2, pageSize * p);
            ResultSet resultSet = stmt.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                int parentId = resultSet.getInt("parent_id");
                String name = resultSet.getString("name");
                String domain = resultSet.getString("domain");
                int status = resultSet.getInt("status");
                int level = resultSet.getInt("level");
                int tagType = resultSet.getInt("tag_type");
                int siteId = resultSet.getInt("site_id");
                LocalDateTime createAt = resultSet.getTimestamp("create_at").toLocalDateTime();
                LocalDateTime updateAt = resultSet.getTimestamp("update_at").toLocalDateTime();

                // 查重
                Site site; Biz biz; String treeName;
                if (siteId == 1) {
                    site = Site.PCOnline;
                    biz = Biz.PGC;
                    switch (tagType) {
                        case 1: {treeName = "类型标签";break;}
                        case 2: {
                            // 主题标签
                            if (parentId == 0) {treeName = "主题一级";}
                            else {treeName = "主题二级";}
                            break;
                        }
                        case 3: {
                            // 产品标签
                            if (domain.equals("品类") || domain.equals("品牌") || domain.equals("产品")) {treeName = domain;break;}
                            else {log.warn("id:{}-domain有误", id);continue;}
                        }
                        default: {log.warn("id:{}-tagType有误", id);continue;}
                    }
                } else if (siteId == 4) {
                    site = Site.PCBaby;
                    biz = Biz.CMS;
                    switch (tagType) {
                        case 1: {treeName = "内容类型标签";break;}
                        case 2: {treeName = "栏目标签";break;}
                        case 3: {treeName = "时间轴标签";break;}
                        case 4: {treeName = "痛点标签";break;}
                        default: {log.warn("id:{}-tagType有误", id);continue;}
                    }
                } else {
                    continue;
                }
                TagTree tagTree = tagTreeRepository.findByNameAndSite(treeName, site);
                if (tagTree == null) {
                    log.warn("id:{}-treeName:{}标签树不存在,", id, treeName);
                    continue;
                }

                Tag left = null;
                if (parentId != 0) {
                    left = tagService.getTagById((long) parentId);
                    if (left == null) {
                        log.warn("id:{},parentId{}有误", id, parentId);
                        continue;
                    }
                }

                Tag tag = new Tag();
                tag.setId((long) id);
                tag.setSite(site);
                tag.setBiz(biz);
                tag.setName(domain + ":" + name);
                tag.setOnShelve(status == 1);
                tag.setCreatedAt(createAt);
                tag.setUpdatedAt(updateAt);
                tag.setTreeId(tagTree.getId());
                tag = tagRepository.saveAndFlush(tag);

                TagDTO tagDTO = TagDTO.from(tag);
                tagDTO.setTreeName(tagTree.getName());
                RestStatus restStatus = esCurdService.insertWithId(TagConstants.TAG_IDX, String.valueOf(tagDTO.getId()), tagDTO);
                if (RestStatus.OK.getStatus() != restStatus.getStatus() && RestStatus.CREATED.getStatus() != restStatus.getStatus()) {
                    log.warn("id:{},标签插入失败:{}", id, tagDTO);
                }

                if ( i++ % 1000 == 0) {
                    log.info("已导入标签数量{}条，执行时间{}", i, Duration.between(LocalDateTime.now(), t));
                }

                if (parentId != 0) {
                    TagRelation.TagRelationKey key = TagRelation.TagRelationKey.builder().left(left).right(tag).build();
                    TagRelation tagRelation = new TagRelation();
                    tagRelation.setRelationKey(key);
                    tagRelation.setRelation("从属关系");
                    tagRelation.setCreatedAt(createAt);
                    tagRelationRepository.save(tagRelation);
                }

            }
            JDBCUtils.close(resultSet, null, null);
        }
        JDBCUtils.close(null, stmt, conn);
        log.info("标签导入已完成");
    }


    public void importTagsFromRacDB(int from, int to) {
        Connection conn = OJDBCUtils.getConnection();
        PreparedStatement pstm = null;
        Timestamp fromTimestamp = Timestamp.valueOf(LocalDateTime.now().minus(from, ChronoUnit.DAYS));
        Timestamp toTimestamp = Timestamp.valueOf(LocalDateTime.now().minus(to, ChronoUnit.DAYS));
        int i = 0; LocalDateTime t = LocalDateTime.now();
        long start = System.currentTimeMillis();
        Map<String, Long> tagTreeIdMap = getTagTreeIdMap();
        try {
            // 保存品类标签
            String sql = "SELECT ID,NAME,STATUS,PUB_STATUS,CREATE_DATE,LAST_UPDATE_DATE FROM PDL_PRODUCT_TYPE WHERE TYPE=2";
            pstm = conn.prepareStatement(sql);
            ResultSet result = pstm.executeQuery();
            while (result.next()) {
                int id = result.getInt("ID");
                Long pcpId = TagIdGenerator.createPcpTagId("102", id);
                String name = result.getString("NAME");
                long last_update_date = Duration.between(LocalDateUtils.dateToLocalDateTime(result.getTimestamp("LAST_UPDATE_DATE")), LocalDateTime.now()).toDays();
                if (from == 0 || (last_update_date <= from && last_update_date > to)) {
                    Tag tagById = tagService.getTagById(pcpId);
                    if (tagById == null || tagById.getUpdatedAt() != LocalDateUtils.dateToLocalDateTime(result.getTimestamp("LAST_UPDATE_DATE"))) {
                        saveProductTag(result, pcpId, "品类", tagTreeIdMap.get("品类"),0, name);

                    }
                    if ( i++ % 1000 == 0) {
                        log.info("品类标签id:{}，name:{}保存成功", pcpId, name);
                        log.info("已导入标签数量{}条，执行时间{}", i, Duration.between(LocalDateTime.now(), t));
                    }
                }

                // 保存品牌标签
                String sql2 = "SELECT ID,NAME,SHORT_NAME,STATUS,PUB_STATUS,CREATE_DATE,LAST_UPDATE_DATE FROM PDL_PRODUCT_TYPE WHERE TYPE=3 AND PARENT_ID=?";
                PreparedStatement pstm2;
                pstm2 = conn.prepareStatement(sql2);
                pstm2.setInt(1, id);
                ResultSet result2 = pstm2.executeQuery();
                while (result2.next()) {
                    int id2 = result2.getInt("ID");
                    Long pcpId2 = TagIdGenerator.createPcpTagId("103", id2);
                    String name2 = result2.getString("NAME");
                    String shortName2 = result2.getString("SHORT_NAME");
                    long last_update_date2 = Duration.between(LocalDateUtils.dateToLocalDateTime(result2.getTimestamp("LAST_UPDATE_DATE")), LocalDateTime.now()).toDays();
                    if (from == 0 || (last_update_date2 <= from && last_update_date2 > to)) {
                        Tag tagById2 = tagService.getTagById(pcpId2);
                        if (tagById2 == null || tagById2.getUpdatedAt() != LocalDateUtils.dateToLocalDateTime(result2.getTimestamp("LAST_UPDATE_DATE"))) {
                            saveProductTag(result2, pcpId2, "品牌", tagTreeIdMap.get("品牌"), pcpId, name2);

                        }
                        if ( i++ % 1000 == 0) {
                            log.info("品牌标签id:{}，name:{}保存成功", pcpId2, name2);
                            log.info("已导入标签数量{}条，执行时间{}", i, Duration.between(LocalDateTime.now(), t));
                        }
                    }

                    // 保存产品标签
                    String sql3 = "SELECT ID,MODEL_NAME,NAME,STATUS,PUB_STATUS,CREATE_DATE,LAST_UPDATE_DATE FROM PDL_PRODUCT_BASE WHERE TYPE_ID=?";
                    PreparedStatement pstm3;
                    if (from > 0) {
                        sql3 += " AND LAST_UPDATE_DATE >= ? AND LAST_UPDATE_DATE < ?";
                        pstm3 = conn.prepareStatement(sql3);
                        pstm3.setInt(1, id2);
                        pstm3.setTimestamp(2, fromTimestamp);
                        pstm3.setTimestamp(3, toTimestamp);
                    } else {
                        pstm3 = conn.prepareStatement(sql3);
                        pstm3.setInt(1, id2);
                    }
                    ResultSet result3 = pstm3.executeQuery();
                    while (result3.next()) {
                        int id3 = result3.getInt("ID");
                        Long pcpId3 = TagIdGenerator.createPcpTagId("101", id3);
                        String name3 = result3.getString("NAME");
                        String modelName3 = result3.getString("MODEL_NAME");
                        if (StringUtils.isBlank(modelName3)) {
                            modelName3 = this.makeItemModelName(name3, name2, shortName2);
                        }
                        Tag tagById3 = tagService.getTagById(pcpId3);
                        if (tagById3 == null || tagById3.getUpdatedAt() != LocalDateUtils.dateToLocalDateTime(result3.getTimestamp("LAST_UPDATE_DATE"))) {
                            saveProductTag(result3, pcpId3, "产品", tagTreeIdMap.get("产品"), pcpId2, modelName3);

                        }
                        if ( i++ % 1000 == 0) {
                            log.info("产品标签id:{}，name:{}保存成功", pcpId3, name3);
                            log.info("已导入标签数量{}条，执行时间{}", i, Duration.between(LocalDateTime.now(), t));
                        }
                    }
                    OJDBCUtils.close(result3, pstm3);
                }
                OJDBCUtils.close(result2, pstm2);
            }
            OJDBCUtils.close(result, pstm, conn);
            log.info("成功保存所有标签累计{}个，耗时{}s..", i, (System.currentTimeMillis() - start) / 1000);
        } catch (Exception e) {
            OJDBCUtils.close(pstm, conn);
            log.error("标签导入失败:", e);
        }
    }


    /**
     * 根据查询集保存或更新产品标签
     *
     * @param resultSet
     * @param tagId
     * @param treeName
     * @param treeId
     * @param parentId
     * @param name
     * @throws Exception
     */
    @Transactional
    public void saveProductTag(ResultSet resultSet, long tagId, String treeName, long treeId, long parentId, String name) throws Exception {
        TagDTO tagDTO = new TagDTO();
        tagDTO.setId(tagId);
        tagDTO.setSite(Site.PCOnline);
        tagDTO.setBiz(Biz.PGC);
        tagDTO.setName(treeName + ":" + name);
        tagDTO.setOnShelve(resultSet.getInt("PUB_STATUS") == 1 && resultSet.getInt("STATUS") == 1);
        tagDTO.setUpdatedAt(LocalDateUtils.dateToLocalDateTime(resultSet.getTimestamp("LAST_UPDATE_DATE")));
        tagDTO.setCreatedAt(LocalDateUtils.dateToLocalDateTime(resultSet.getTimestamp("CREATE_DATE")));
        tagDTO.setTreeId(treeId);
        tagDTO.setTreeName(treeName);
        tagService.createTag(tagDTO);

        if (parentId != 0) {
            Tag left = new Tag();
            left.setId(parentId);
            Tag right = new Tag();
            right.setId(tagId);
            TagRelationDTO tagRelationDTO = new TagRelationDTO();
            tagRelationDTO.setLeftId(parentId);
            tagRelationDTO.setRightId(tagId);
            tagRelationDTO.setCreatedAt(LocalDateTime.now());
            tagRelationDTO.setRelation("从属关系");
            tagRelationService.createOrUpdateTagRelation(tagRelationDTO, left, right);
        }
    }

    private String makeItemModelName(String itemName, String brandName, String brandShortName) {
        if (itemName.contains(brandName)) {
            return itemName.replace(brandName, "").trim();
        } else if (StringUtils.isNotBlank(brandShortName) && itemName.contains(brandShortName)) {
            return itemName.replace(brandShortName, "").trim();
        } else {
            return itemName;
        }
    }

    private Map<String, Long> getTagTreeIdMap () {
        HashMap<String, Long> map = new HashMap<>();
        for (String treeName : Arrays.asList("品类", "品牌", "产品")) {
            TagTree tagTree = tagTreeRepository.findByNameAndSite(treeName, Site.PCOnline);
            if (tagTree == null) {
                log.warn("treeName:{}标签树不存在,", treeName);
                return new HashMap<>();
            }
            map.put(treeName, tagTree.getId());
        }
        return map;
    }

}
